package com.zhiyou.app.shop.website.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.stereotype.Component;

import com.zhiyou.app.shop.manager.dao.impl.BaseHibernateDAO;
import com.zhiyou.app.shop.website.dao.IEpListDao;
import com.zhiyou.app.shop.website.dao.pojo.EpList;

@Component
public class EpListDaoImpl extends BaseHibernateDAO<EpList, String> implements
		IEpListDao {
	
	public EpListDaoImpl(){
		super(EpList.class);
	}

	public List findEpSale(final String status, final String account,final int startNum,final int pagesize) {
		final String sql = "select u.user_account,deal_amount,bank_name,bank_province,credit_level,ep.id,ep.post_fee from user u,user_bank ub,ep_list ep " +
				"where u.user_account=ub.user_id and u.user_account=ep.user_id and ep.status = ? and ep.user_id <> ? order by ep.create_date limit ?,? ";
		List list = hibernateTemplate.execute(new HibernateCallback<List>() {
			public List doInHibernate(Session session)
					throws HibernateException {
				Query query = session.createSQLQuery(sql);
				query.setString(0, status);
				query.setString(1, account);
				query.setInteger(2, startNum);
				query.setInteger(3, pagesize);
				query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				return query.list();
			}
		});
		return list;
	}

	public Integer findEpSaleCnt(final String status,final String account){
		final String sql = "select count(1) as cnt from user u,user_bank ub,ep_list ep "+
						" where u.user_account=ub.user_id and u.user_account=ep.user_id and ep.status = ? and ep.user_id <> ? ";
		Integer epcnt = hibernateTemplate.execute(new HibernateCallback<Integer>() {
			public Integer doInHibernate(Session session)
					throws HibernateException {
				SQLQuery query = session.createSQLQuery(sql);
				query.setString(0, status);
				query.setString(1, account);
				return (Integer)query.addScalar("cnt", new org.hibernate.type.IntegerType()).uniqueResult();
			}
		});
		return epcnt;
	}
	
	public List findepByuser(final String account,final int startNum,final int pagesize) {
		final String sql = "select a.* from (select e1.id, e1.user_id, '卖出' tradetype, e1.type, (e1.deal_amount+e1.post_fee) aomount," +
				"e1.deal_amount,e1.status,e1.deal_date,e1.create_date,e1.success_date, e1.relation_id as relation_id,e1.complain_status from ep_list e1 where e1.user_id=? union " +
				"select e2.id, e2.relation_id user_id,'买入' tradetype, e2.type, (e2.deal_amount+e2.post_fee) aomount,e2.deal_amount,e2.status,e2.deal_date,e2.create_date,e2.success_date,e2.user_id as relation_id,e2.complain_status " +
				"from ep_list e2 where e2.relation_id=? ) a order by a.deal_date desc limit ?, ?";
		List list = hibernateTemplate.execute(new HibernateCallback<List>() {
			public List doInHibernate(Session session)
					throws HibernateException {
				SQLQuery query = session.createSQLQuery(sql);
				query.setString(0, account);
				query.setString(1, account);
				query.setInteger(2, startNum);
				query.setInteger(3, pagesize);
				query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				return query.list();
			}
		});
		return list;
	}

	public Integer findepCntByuser(final String account) {
		final String sql = "select count(1) as cnt from (select e1.user_id, '卖出' type,(e1.deal_amount+e1.post_fee)," +
				"e1.deal_amount,e1.status,e1.deal_date,e1.create_date,e1.success_date from ep_list e1 where e1.user_id=? " +
				"union select e2.relation_Id user_id,'买入' type,(e2.deal_amount+e2.post_fee),e2.deal_amount," +
				"e2.status,e2.deal_date,e2.create_date,e2.success_date from ep_list e2 where e2.relation_id=?) a";
		Integer list = hibernateTemplate.execute(new HibernateCallback<Integer>() {
			public Integer doInHibernate(Session session)
					throws HibernateException {
				SQLQuery query = session.createSQLQuery(sql);
				query.setString(0, account);
				query.setString(1, account);
				return (Integer)query.addScalar("cnt", new org.hibernate.type.IntegerType()).uniqueResult();
			}
		});
		return list;	
	}

	public List findEpList(final String buy, final String sale, final int startNum, final int pagesize) {
		List list = hibernateTemplate.execute(new HibernateCallback<List>() {
			public List doInHibernate(Session session) throws HibernateException {
				String sql = "SELECT ep.*,ec.id complainId,ec.handle FROM ep_list ep LEFT JOIN ep_complain ec " +
						"ON ep.id=ec.ep_id where 1=1 ORDER BY ep.create_date DESC ";
				if(buy != null && !"".equals(buy)){
					sql += " and ep.user_id = '"+buy+"'";
				}
				if(sale != null && !"".equals(sale)){
					sql += " and ep.relation_id = '"+sale+"'";
				}
				sql += " limit ?, ?";
				SQLQuery query = session.createSQLQuery(sql);
				query.setInteger(0, startNum);
				query.setInteger(1, pagesize);
				query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				return query.list();
			}
		});
		
		
		return list;
	}

	public Integer findEpList(final String buy, final String sale) {
		Integer cnt = hibernateTemplate.execute(new HibernateCallback<Integer>() {
			public Integer doInHibernate(Session session)
					throws HibernateException {
				String sql = "SELECT COUNT(1) cnt FROM ep_list ep LEFT JOIN ep_complain ec ON ep.id=ec.ep_id where 1=1 ";
				if(buy != null && !"".equals(buy)){
					sql += " and ep.user_id = '"+buy+"'";
				}
				if(sale != null && !"".equals(sale)){
					sql += " and ep.relation_id = '"+sale+"'";
				}
				SQLQuery query = session.createSQLQuery(sql);
				return (Integer)query.addScalar("cnt", new org.hibernate.type.IntegerType()).uniqueResult();
			}
		});
		return cnt;
	}
}
